import os
import pickle
import sqlite3
import logging


logger = logging.getLogger(__name__)


class Database:
    init_sql = [
        '''create table space (
            x integer not null,
            y integer not null,
            z integer not null,
            data blob,
            primary key (x, y, z))''',
        '''create table user (
            id text primary key,
            data blob)'''
    ]


    def __init__(self, db_path = None):
        self.db_path = db_path
        self.conn = None

        if not self.db_path:
            cur_path = os.path.dirname(os.path.abspath(__file__))
            self.db_path = os.path.join(cur_path, 'z.db.sqlite3')

        if not os.path.exists(self.db_path):
            self._init_db()
            logger.info(f'Database initialized: {self.db_path}')


    def __enter__(self):
        self.conn = sqlite3.connect(self.db_path)
        cur = self.conn.cursor()
        cur.execute('create table if not exists foo(bar)')
        cur.close()
        return self


    def __exit__(self, exc_type, exc_val, exc_tb):
        self.conn.close()


    def _init_db(self):
        conn = sqlite3.connect(self.db_path)
        cur = conn.cursor()

        for sql in self.init_sql:
            logger.debug(sql.split()[0])
            cur.execute(sql)

        conn.commit()
        cur.close()
        conn.close()


    def save_spaces(self, space:dict):
        '''
        Argv:
            space - {(x, y, z): {data}, ...}
        '''
        data = [k + (pickle.dumps(v),) for k, v in space.items() if v.get('dirty', True)]
        if not data:
            logger.info('no space data to save')
            return

        sql = 'insert or replace into space (x, y, z, data) values (?, ?, ?, ?)'
        cur = self.conn.cursor()
        cur.executemany(sql, data)
        cur.close()
        self.conn.commit()


    def save_users(self, user:dict):
        '''
        Argv:
            user - {id: {data}, ...}
        '''
        data = [(k, pickle.dumps(v)) for k, v in user.items() if v.get('dirty', True)]
        if not data:
            logger.info('no user data to save')
            return

        sql = 'insert or replace into user (id, data) values (?, ?)'
        cur = self.conn.cursor()
        cur.executemany(sql, data)
        cur.close()
        self.conn.commit()


    def get_spaces(self):
        sql = 'select x, y, z, data from space'
        cur = self.conn.cursor()
        cur.execute(sql)
        res = cur.fetchall()
        cur.close()
        return {(r[0], r[1], r[2]): pickle.loads(r[3]) for r in res}
        

    def get_users(self):
        sql = 'select id, data from user'
        cur = self.conn.cursor()
        cur.execute(sql)
        res = cur.fetchall()
        cur.close()
        return {r[0]: pickle.loads(r[1]) for r in res}


    def save_space(self, position, data):
        '''
        Argv:
            position - (x, y, z)
            data - 
        '''
        data = position + (pickle.dumps(data),)
        sql = 'insert or replace into space (x, y, z, data) values (?, ?, ?, ?)'
        cur = self.conn.cursor()
        cur.execute(sql, data)
        cur.close()
        self.conn.commit()


    def save_user(self, uid, data):
        '''
        Argv:
            uid -
            data -
        '''
        data = (uid, pickle.dumps(data))
        sql = 'insert or replace into user (id, data) values (?, ?)'
        cur = self.conn.cursor()
        cur.execute(sql, data)
        cur.close()
        self.conn.commit()


    def get_space(self, position):
        sql = 'select x, y, z, data from space'
        sql += f' where x={position[0]} and y={position[1]} and z={position[2]}'
        cur = self.conn.cursor()
        cur.execute(sql)
        res = cur.fetchall()
        cur.close()
        return {(r[0], r[1], r[2]): pickle.loads(r[3]) for r in res}
        

    def get_user(self, uid):
        sql = 'select id, data from user'
        sql += f" where id='{uid}'"
        cur = self.conn.cursor()
        cur.execute(sql)
        res = cur.fetchall()
        cur.close()
        return {r[0]: pickle.loads(r[1]) for r in res}


